# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/en/latest/topics/item-pipeline.html
import json

from digkey_icnet.items import PartsDetailItem
from digkey_icnet.items import PartsDollarItem
from digkey_icnet.items import PartsRMBItem
from digkey_icnet.items import DollorRateItem
from digkey_icnet.items import CompanyinfoItem
from digkey_icnet.items import ICnettotalItem

import pymysql

class DigkeyIcnetPipeline(object):
    collection_name = 'scrapy_items'

    def __init__(self, MYSQL_URI, MYSQL_USER,MYSQL_PSW,MYSQL_DB):
        self.mysql_uri = MYSQL_URI
        self.mysql_db = MYSQL_DB
        self.mysql_user = MYSQL_USER
        self.mysql_psw = MYSQL_PSW

    @classmethod
    def from_crawler(cls, crawler):
        return cls(
            MYSQL_URI=crawler.settings.get('MYSQL_URI'),
            MYSQL_DB=crawler.settings.get('MYSQL_DB'),
            MYSQL_USER=crawler.settings.get('MYSQL_USER'),
            MYSQL_PSW=crawler.settings.get('MYSQL_PSW')
        )

    def open_spider(self, spider):
        self.conn = pymysql.connect(host=self.mysql_uri,user=self.mysql_user,password=self.mysql_psw,
                                    db=self.mysql_db,port=3306,charset='utf8')
        self.cursor=self.conn.cursor()

    def close_spider(self, spider):
        # self.cursor.close()
        self.conn.close()

    def process_item(self, item, spider):
        # 去重，如果有就更新，没有就插入
        print("***********************************************************")
        try:
            if isinstance(item,PartsDetailItem):
                print("PartsDetailItem")
                effect_row = self.cursor.execute("REPLACE into parts_detail(Part_Number,Manufacturer,Encapsulation,class1,class2,img_url,url,dj_Part_Number)values(%s,%s,%s,%s,%s,%s,%s,%s)",
                                                 (item['Part_Number'],item['Manufacturer'],item['Encapsulation'],item['class1'],item['class2'],item['img_url'],item['url'],item['dj_Part_Number']))
                self.conn.commit()

            elif isinstance(item,PartsDollarItem):
                print("PartsDollarItem")
                effect_row = self.cursor.execute("REPLACE into parts_price_dollar(dj_Part_Number,Part_Number,Num,Num_Low,Num_Higher,Unit_Price_Dollar,Grab_Time)values(%s,%s,%s,%s,%s,%s,%s)",
                                                 (item['dj_Part_Number'],item['Part_Number'],item['Number'],item['Num_Low'],item['Num_Higher'],item['Unit_Price_Dollar'],item['Grab_Time']))
                self.conn.commit()

            elif isinstance(item,CompanyinfoItem):
                print("CompanyinfoItem")
                effect_row = self.cursor.execute(
                    "REPLACE into company_info(Company_Name,Part_Number,Levels,User_Date,ContactMan,Telephone,Mobilephone,Facsimile,Office_address,QQ,address,Batch_Number,Inventory,Pacakage,Remarks,Issue_Date,Grap_Date)values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
                    (item['Name'], item['Part_Number'], item['Level'], item['User_Date'], item['ContactMan'],
                     item['Telephone'],
                     item['Mobilephone'], item['Facsimile'], item['Office_address'], item['QQ'], item['address'],
                     item['Batch_Number'],
                     item['Inventory'], item['Pacakage'], item['Remarks'], item['Issue_Date'], item['Grap_Date']))
                self.conn.commit()

            elif isinstance(item,ICnettotalItem):
                print("ICnettotalItem")
                effect_row = self.cursor.execute(
                    "REPLACE into icnet_company_total(page_total,ICCP_SSCP_total,Parts_Number)values(%s,%s,%s)",
                    (
                    item['page_total'], item['ICCP_SSCP_total'], item['Parts_Number']
                    ))
                self.conn.commit()

        except Exception as e:
            print(e)
            self.conn.rollback()

        return item




